﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using Entity;
using IRepository;

namespace Repository
{
    public class InventoryListRepository : IInventoryListRepository
    {
        private readonly IDbConnection _dbConnection;

        public InventoryListRepository(IDbConnection dbConnection)
        {
            _dbConnection = dbConnection;
        }

        public async Task<InventoryListEntity> AddAsync(InventoryListEntity entity)
        {
            var sql = @"INSERT INTO wms_knzy_inventory_list (
            inventory_order_no, inventory_types, inventory_start_time,
            stkt_end_time, warehouse_name, handler_id,
            note, review_status, create_by, create_time, is_delete,
            outbound_status, storage_status, reviewer, review_time,
            organizations_id, update_by, update_time  -- 新增：补充organizations_id字段
        ) VALUES (
            @inventory_order_no, @inventory_types, @inventory_start_time,
            @stkt_end_time, @warehouse_name, @handler_id,
            @note, @review_status, @create_by, GETDATE(), 0,
            @outbound_status, @storage_status, @reviewer, GETDATE(),
            @organizations_id, @update_by, @update_time   -- 新增：绑定organizations_id参数
        );
        SELECT CAST(SCOPE_IDENTITY() AS INT);";

            entity.inventory_list_id = await _dbConnection.QueryFirstAsync<int>(sql, new
            {
                entity.inventory_order_no,
                entity.inventory_types,
                entity.inventory_start_time,
                entity.stkt_end_time,
                entity.warehouse_name,
                entity.handler_id,
                entity.note,
                entity.review_status,
                entity.create_by,
                entity.outbound_status,
                entity.storage_status,
                entity.reviewer,
                entity.organizations_id,  // 新增：传递organizations_id参数
                 entity.update_by,    // 传递 update_by 参数
                entity.update_time   // 传递 update_time 参数
            });
            return entity;
        }

        public async Task<InventoryListEntity> GetByIdAsync(int id)
        {
            var sql = @"SELECT * FROM wms_knzy_inventory_list 
                        WHERE inventory_list_id = @Id AND is_delete = 0";
            return await _dbConnection.QueryFirstOrDefaultAsync<InventoryListEntity>(sql, new { Id = id });
        }

        public async Task<InventoryListEntity> GetByOrderNoAsync(string orderNo)
        {
            var sql = @"SELECT * FROM wms_knzy_inventory_list 
                        WHERE inventory_order_no = @OrderNo AND is_delete = 0";
            return await _dbConnection.QueryFirstOrDefaultAsync<InventoryListEntity>(sql, new { OrderNo = orderNo });
        }

        public async Task UpdateAsync(InventoryListEntity entity)
        {
            // 修正：SQL中使用 inventory_types 替换 check_type
            var sql = @"UPDATE wms_knzy_inventory_list SET
                            inventory_types = @inventory_types,  -- 替换 check_type 为 inventory_types
                            inventory_start_time = @inventory_start_time,
                            stkt_end_time = @stkt_end_time,
                            warehouse_name = @warehouse_name,
                            handler_id = @handler_id,
                            note = @note,
                            review_status = @review_status,
                            reviewer = @reviewer,
                            review_time = @review_time,
                            update_by = @update_by,
                            update_time = GETDATE()
                        WHERE inventory_list_id = @inventory_list_id";

            await _dbConnection.ExecuteAsync(sql, entity);
        }

        public async Task DeleteByListIdAsync(int listId)
        {
            var sql = @"UPDATE wms_knzy_inventory_list 
                       SET is_delete = 1, update_time = GETDATE()
                       WHERE inventory_list_id = @ListId";
            await _dbConnection.ExecuteAsync(sql, new { ListId = listId });
        }

        public async Task<PaginationResult<InventoryListEntity>> GetPagedListAsync(int pageIndex, int pageSize, Dictionary<string, object> filter, string sort)
        {
            var where = "WHERE is_delete = 0";
            var parameters = new DynamicParameters();

            // 处理过滤条件（原有逻辑）
            foreach (var item in filter)
            {
                string dbField = item.Key == "check_type" ? "inventory_types" : item.Key;
                where += $" AND {dbField} = @{item.Key}";
                parameters.Add(item.Key, item.Value);
            }

            // 关键修复：校验并处理排序参数
            string validSort = ProcessSortParameter(sort);

            // 构建SQL时使用校验后的排序语句
            var sql = $@"SELECT COUNT(*) FROM wms_knzy_inventory_list {where};
                 SELECT * FROM wms_knzy_inventory_list {where} ORDER BY {validSort}
                 OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

            parameters.Add("Offset", (pageIndex - 1) * pageSize);
            parameters.Add("PageSize", pageSize);

            using (var multi = await _dbConnection.QueryMultipleAsync(sql, parameters))
            {
                var total = await multi.ReadFirstAsync<int>();
                var data = (await multi.ReadAsync<InventoryListEntity>()).AsList();
                return new PaginationResult<InventoryListEntity>
                {
                    Total = total,
                    Data = data,
                    PageIndex = pageIndex,
                    PageSize = pageSize
                };
            }
        }

        // 新增：处理排序参数的方法
        private string ProcessSortParameter(string sort)
        {
            // 1. 默认为按创建时间降序
            if (string.IsNullOrWhiteSpace(sort))
                return "create_time DESC";

            // 2. 拆分排序字段和方向（支持逗号分隔多个排序条件）
            var sortParts = sort.Split(',')
                .Select(part => part.Trim())
                .Where(part => !string.IsNullOrEmpty(part))
                .ToList();

            if (!sortParts.Any())
                return "create_time DESC";

            // 3. 校验每个排序项
            var validParts = new List<string>();
            foreach (var part in sortParts)
            {
                // 拆分字段名和排序方向（如"create_time DESC"拆分为["create_time", "DESC"]）
                var fieldAndDir = part.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
                if (fieldAndDir.Length == 0)
                    continue;

                string field = fieldAndDir[0].Trim();
                string dir = fieldAndDir.Length > 1 ? fieldAndDir[1].Trim().ToUpper() : "ASC";

                // 4. 校验字段是否在白名单中，方向是否合法
                if (_validSortFields.Contains(field) && (dir == "ASC" || dir == "DESC"))
                {
                    validParts.Add($"{field} {dir}");
                }
            }

            // 5. 若没有合法排序项，使用默认值
            return validParts.Any() ? string.Join(", ", validParts) : "create_time DESC";
        }
        // 允许排序的字段白名单（根据实际表结构调整）
        private readonly List<string> _validSortFields = new List<string>
{
    "create_time", "inventory_order_no", "warehouse_name", "review_status"
};
        public async Task<InventoryListEntity> GetOngoingInventoryByWarehouseAsync(string warehouseName)
        {
            var sql = @"SELECT TOP 1 * FROM wms_knzy_inventory_list 
                        WHERE warehouse_name = @WarehouseName 
                          AND is_delete = 0 
                          AND review_status IN (@Status1, @Status2)";
            return await _dbConnection.QueryFirstOrDefaultAsync<InventoryListEntity>(sql, new
            {
                WarehouseName = warehouseName,
                Status1 = InventoryListEntity.STATUS_DRAFT,
                Status2 = InventoryListEntity.STATUS_PENDING_REVIEW
            });
        }
    }
}